<?php

$link = mysqli_connect(
    '127.0.0.1',
    'root',
    '123456',
    'apiadmin');

if (!$link) {
    printf("Can't connect to MySQL Server. Errorcode: %s ", mysqli_connect_error());
    exit;
}else{
    /*读取excel*/
    $file = fopen('phone_num.csv','r');
    while ($data = fgetcsv($file)) { //每次读取CSV里面的一行内容
        $goods_list[] = $data;
    }
    fclose($file);

    foreach ($goods_list as &$val){
        /*获取运营商和归属地,使用阿里云的手机归属地查询*/
        $appcode   = '98b3d3fc438f443397227f8156de6603';
        $appsecret = 'dibba1ldg8cvkp2j9ovajj9b3bsr0x1h';
        $url      = 'https://jshmgsdmfb.market.alicloudapi.com/shouji/query';
        $getMobileAreaInfo =  getMobileArea($val[0],$appcode,$url);
        $getMobileAreaInfo = json_decode($getMobileAreaInfo);
        if ($getMobileAreaInfo->status == 0) {
            $val['province']= $province = $getMobileAreaInfo->result->province;
            $val['city']= $city = $getMobileAreaInfo->result->city;
            $val['company']= $company = $getMobileAreaInfo->result->company;
        }else{
            echo "获取手机号码归属地错误" ;
        }

        $val['phone'] = $val[0];
        var_dump($val['phone']);
        $val['add_time'] = date('Y-m-d h:i:s', time());
        /*写入数据库*/
        $sql="INSERT INTO user_phone ( phone_num, company,province,city,add_time )
                       VALUES (".$val['phone'].",".$val['company'].",".$val['province'].",".$val['city'].",".$val['add_time'].")";
        if ($link->query($sql) == TRUE){
            echo '插入成功';
        }else{
            echo '插入失败';
        }

    }


}

function getMobileArea($moblie,$appcode,$host)
{
    $path = "/mobile";
    $method = "GET";
//    $appcode = "98b3d3fc438f443397227f8156de6603";

    $headers = array();
    array_push($headers, "Authorization:APPCODE " . $appcode);
    $querys = "mobile=" . $moblie;

    $url = $host . $path . "?" . $querys;
    $curl = curl_init();
    curl_setopt($curl, CURLOPT_CUSTOMREQUEST, $method);
    curl_setopt($curl, CURLOPT_URL, $url);
    curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
    curl_setopt($curl, CURLOPT_FAILONERROR, false);
    curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($curl, CURLOPT_HEADER, false);
    if (1 == strpos("$" . $host, "https://")) {
        curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
    }

    $data = curl_exec($curl);                                 //运行curl
    curl_close($curl);
    return $data;
}
/* Close the connection 关闭连接*/
mysqli_close($link);

